package DB;

import Model.Supplier;

import java.sql.*;
import java.util.ArrayList;

public class SupplierDB implements SupplierDBIF {

    private Connection conn;

    public SupplierDB() {
        conn = ConnectionDB.getInstance().getConn();
    }

    @Override
    public int insertSupplier(Supplier supplier) {
        int rc = -1;
        PreparedStatement pstmt = null;

        String insert = "INSERT INTO Supplier (id, name, address, country, phoneNo, email)" + "VALUES (?,?,?,?,?,?)";

        System.out.println(insert);

        try {
            pstmt = conn.prepareStatement(insert);
            pstmt.setInt(1, supplier.getId());
            pstmt.setString(2, supplier.getName());
            pstmt.setString(3, supplier.getAddress());
            pstmt.setString(4, supplier.getCountry());
            pstmt.setInt(5, supplier.getPhoneNo());
            pstmt.setString(6, supplier.getEmail());
            rc = pstmt.executeUpdate();
        }
        catch (SQLException sqlE) {
            System.out.println("SQL Error: " + sqlE.getMessage());
        }
        catch (Exception e) {
            e.getMessage();
        }

        return rc;
    }

    @Override
    public Supplier findSupplier(int id) {
        return singleWhere("id = " + id, false);
    }

    @Override
    public ArrayList<Supplier> getAllSuppliers(boolean retrieveAssociation) {
        return miscWhere("", false);
    }

    @Override
    public int updateSupplier(Supplier supplier) {

        Supplier supObj = supplier;
        int rc = -1;

        String query="UPDATE Supplier SET " +
                "id ='" + supObj.getId() + "', " +
                "name ='" +supObj.getName() + "', " +
                "address ='" + supObj.getAddress() + "', " +
                "country ='" + supObj.getCountry() + "', " +
                "phoneNo ='" + supObj.getPhoneNo() + "', " +
                "email ='" + supObj.getEmail() + "' " +
                " WHERE id = " + supObj.getId() + "";

        System.out.println("Update query: " + query);

        try {
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }
        catch(Exception e){
            System.out.println("Update exception in supplier db: " +e);
        }
        return(rc);
    }

    @Override
    public int deleteSupplier(int id) {
        int rc = -1;

        String query = "DELETE FROM Supplier WHERE id = '" + id + "'";

        System.out.println(query);

        try {
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }
        catch(Exception e){
            System.out.println("Delete exception in supplier db: " + e);
        }
        return(rc);
    }

    //Finding a single supplier
    private Supplier singleWhere(String wClause, boolean retrieveAssociation) {
        ResultSet results;
        Supplier supObj = new Supplier();

        String query = buildQuery(wClause);
        System.out.println(query);

        try {
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            if (results.next()) {
                supObj = buildSupplier(results);
                stmt.close();
            }
            else {
                supObj = null;
            }
        }
        catch (Exception e) {
            System.out.println("Query exception: " + e);
        }

        return supObj;
    }

    //Finding all suppliers
    private ArrayList<Supplier> miscWhere(String wClause, boolean retrieveAssociation) {
        ResultSet results;
        ArrayList<Supplier> list = new ArrayList<Supplier>();

        String query = buildQuery(wClause);

        try {
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            while (results.next()) {
                Supplier supObj = new Supplier();
                supObj = buildSupplier(results);
                list.add(supObj);
            }

            stmt.close();
        }
        catch (Exception e) {
            System.out.println("Query exception: " + e);
            e.printStackTrace();
        }

        return list;
    }

    //Method to build the query
    private String buildQuery(String wClause) {
        String query = "SELECT id, name, address, country, phoneNo, email FROM Supplier";

        if (wClause.length()>0) {
            query = query + " WHERE " + wClause;
        }

        return query;
    }

    //Method to build a supplier object
    private Supplier buildSupplier(ResultSet results) {

        Supplier supObj = new Supplier();

        try {
            supObj.setId(results.getInt("id"));
            supObj.setName(results.getString("name"));
            supObj.setAddress(results.getString("address"));
            supObj.setCountry(results.getString("country"));
            supObj.setPhoneNo(results.getInt("phoneNo"));
            supObj.setEmail(results.getString("email"));
        }
        catch (Exception e) {
            System.out.println("Error building supplier object");
        }

        return supObj;
    }

    public int supplierGetMax() {
        int nextId = GetMax.getMaxId("SELECT max(id) from Supplier");

        nextId = nextId +1;

        return nextId;
    }
}
